import os
import random
import pymysql
import datetime
import pandas as pd
from sqlalchemy import create_engine


pd.set_option('display.max_columns', 60)
d = os.path.dirname('.')
run_dir = os.path.abspath(d)
os.chdir(run_dir)


# 连接MySQL数据库方式
def connect_method(method='python'):
    if method == 'python':  # Python连接
        conn = pymysql.Connect(
            host='localhost',
            port=3306,
            user='root',
            password='12345',
            database='recommend2',
            charset='utf8'
        )
        return conn
    elif method == 'pandas':  # pandas连接
        conn = create_engine('mysql+pymysql://root:12345@localhost:3306/recommend2?charset=utf8')
        return conn
    else:
        raise TypeError('method无效')


# 作业一
def work_one(conn):
    # sql查询语句
    sql_sale = 'select ' \
               'f1.id as "销售id",' \
               'f1.name as "销售姓名",' \
               'f1.total_count as "沟通用户总数",' \
               'f1.suc1_count as "直接成单数",' \
               'f1.suc2_count as "跟单成功数",' \
               '(f1.suc1_count + f1.suc2_count) as "总成单数",' \
               'f1.suc1_count/f1.total_count as "直接成单率",' \
               'f1.suc2_count/f1.total_count as "跟单成功率",' \
               '((f1.suc1_count + f1.suc2_count) / f1.total_count) as "总成功率",' \
               'f1.fail1_count as "非意向用户数",f1.fail2_count as "跟单失败数",' \
               'f1.fail1_count/f1.total_count as "非意向用户率" ,' \
               'f1.fail2_count/f1.fail1_count as "跟踪失败率" ' \
               'from (select ' \
               'm.id as id,' \
               's.name as name,' \
               'count(s2.content="直接签单成功" or null) as suc1_count,' \
               'count(s2.content="跟单成功" or null) as suc2_count,' \
               'count(s2.content="非意向用户" or null) as fail1_count,' \
               'count(s2.content="跟单失败" or null) as fail2_count,' \
               'count(*) as total_count ' \
               'from mission m ' \
               'inner join salesstaff s on m.salesstaff_id = s.id ' \
               'inner join status s2 on m.status_id = s2.id ' \
               'group by m.salesstaff_id) as f1 ;'
    # 获得MySQL数据
    df = pd.read_sql(
        sql_sale,
        conn,
    )
    # 新特征赋值
    df['底薪'] = 2000
    df['直接成单奖励'] = 0
    df['跟单成功奖励'] = 0
    df['直接成单数最高者奖励'] = 0  # index1 2000
    df['跟单成功率最高着奖励'] = 0  # index2 1600
    df['跟单成功数最高着奖励'] = 0  # index3 1600
    df['总成单数最高者奖励'] = 0  # index4 3000
    df['总成单率最高者奖励'] = 0  # index5 3000
    df['总工资'] = 0
    # 排序
    index1 = df['直接成单数'].sort_values(ascending=False).index.tolist()[0]
    index2 = df['跟单成功率'].sort_values(ascending=False).index.tolist()[0]
    index3 = df['跟单成功数'].sort_values(ascending=False).index.tolist()[0]
    index4 = df['总成单数'].sort_values(ascending=False).index.tolist()[0]
    index5 = df['总成功率'].sort_values(ascending=False).index.tolist()[0]
    df.loc[index1, '直接成单数最高者奖励'] = 2000
    df.loc[index2, '跟单成功率最高着奖励'] = 1600
    df.loc[index3, '跟单成功数最高着奖励'] = 1600
    df.loc[index4, '总成单数最高者奖励'] = 3000
    df.loc[index5, '总成单率最高者奖励'] = 3000
    df['直接成单奖励'] = df['直接成单数'] * 100
    df['跟单成功奖励'] = df['跟单成功数'] * 80
    df['总工资'] = (
        df['底薪'] + df['直接成单奖励'] + df['跟单成功奖励'] + df['直接成单数最高者奖励']
        + df['跟单成功率最高着奖励'] + df['跟单成功数最高着奖励']
        + df['总成单数最高者奖励'] + df['总成单率最高者奖励']
    )
    # 数据导出excel
    df.to_excel('{}/employee_performance_pay.xlsx'.format(run_dir), index=False)
    print('绩效评定数据导出成功')


# 获得派发数据1
def get_mysql_data1(conn):
    # 构建获取未处理的顾客数据sql语句
    sql_untreated = 'select id as customer_id,tel from customer ' \
                    'where id not in (select customer_id from mission group by customer_id);'
    # 获取MySQL顾客数据
    df_customer = pd.read_sql(
        sql_untreated,
        conn,
    )
    # 构建销售人员sql语句
    sql_salesstaff = 'select id from salesstaff;'
    # 获取MySQL销售人员数据
    df_salesstaff = pd.read_sql(
        sql_salesstaff,
        conn,
    )
    # 将顾客数据转为字典
    customer_dict = df_customer.to_dict()
    # 将销售人员id转为列表
    salesstaff_list = df_salesstaff['id'].tolist()
    data = []
    # 枚举销售人员
    for index, salesstaff_id in enumerate(salesstaff_list):
        for i in range(index * 25, (index + 1) * 25):
            this_customer_id = customer_dict['customer_id'][i]
            this_tel = customer_dict['tel'][i]
            data.append({'salesstaff_id': salesstaff_id,
                         'customer_id': this_customer_id,
                         'tel': this_tel})
    return data


# 获得派发数据2
def get_mysql_data2(conn):
    # 此处选取2020-06-30的数据，认为29号继续跟单的数据有可能在30号给出跟单结果，7月1号就不需要继续跟单了
    sql_treated = 'select m.customer_id as customer_id,' \
                  'c.tel as tel,m.salesstaff_id as salesstaff_id ' \
                  'from mission m inner join customer c on m.customer_id = c.id ' \
                  'where status_id = 1 and customer_id in ' \
                  '(select customer_id from mission ' \
                  'where status_id = 1 and createDate = "2020-06-30");'
    # 获取MySQL顾客数据
    df_customer = pd.read_sql(
        sql_treated,
        conn,
    )
    # 构建销售人员sql语句
    sql_salesstaff = 'select id from salesstaff;'
    # 获取MySQL销售人员数据
    df_salesstaff = pd.read_sql(
        sql_salesstaff,
        conn,
    )
    # 销售人员id转为集合
    salesstaff_set = set(df_salesstaff['id'].tolist())
    # 按顾客分组
    customer_grouped = df_customer.groupby('customer_id')
    data = []
    # 遍历每个顾客
    for customer_id, customer_item in customer_grouped:
        # 获取手机号信息
        this_tel = customer_item.iloc[0, 1]
        # 将咨询过的销售人员转为集合
        treaded_staff_id = set(customer_item['salesstaff_id'].values.tolist())
        # 用总销售人员集合减去咨询过销售人员集合得到未咨询过该顾客的销售人员集合
        untreaded_staff_id = list(salesstaff_set - treaded_staff_id)
        # 如果未咨询过该顾客的销售人员为空时，重置总集合
        if not untreaded_staff_id:
            salesstaff_set = set(df_salesstaff['id'].tolist())
            untreaded_staff_id = list(salesstaff_set - treaded_staff_id)
        to_tread_staff_id = random.choice(untreaded_staff_id)
        data.append({'salesstaff_id': to_tread_staff_id,
                     'customer_id': customer_id,
                     'tel': this_tel})
    return data


# 作业二
def work_two(conn):
    data1 = get_mysql_data1(conn)
    data2 = get_mysql_data2(conn)
    data = data1 + data2
    df_data = pd.DataFrame(data)
    df_data.drop('tel', axis=1, inplace=True)
    df_data['createDate'] = '2020-07-01'
    df_data['status_id'] = 7
    df_data.to_excel('{}/smart_dispatch_data.xlsx'.format(run_dir), index=False)
    print('任务拍单数据导出成功')


if __name__ == '__main__':
    # 连接MySQL数据库
    conn_pd = connect_method(method='pandas')
    print('数据库连接成功')
    # 作业一：员工绩效评定
    print('绩效评定中...')
    work_one(conn=conn_pd)
    # 作业二：销售任务智能排单系统
    print('任务拍单中...')
    work_two(conn=conn_pd)



